﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SWDataDB.DAL;
using SWDataDB.Models;
using System.Data.SqlClient;


namespace SWDataDB.DAL
{
    //标本图片操作
    public class BBImageInfo
    {
        /// <summary>
        /// 获取BB_IMAGE_TBL中所有记录的条数
        /// </summary>
        /// <returns></returns>
        public long GetBBImageInfoCount()
        {
            long lCount = 0;
            string strSql = "SELECT COUNT(*) AS count FROM dbo.BB_IMAGE_TBL";
            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            DataRow dr = dt.Rows[0];
            lCount = long.Parse(dr["count"].ToString());

            return lCount;
        }

        /// <summary>
        /// 该标本是否存在
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public bool IsBBImageExist(string file_id, string bb_code)
        {
            string strSql = "SELECT COUNT(*) AS COUNT FROM dbo.BB_IMAGE_TBL WHERE FILE_ID='" + file_id + "' AND BB_CODE='" + bb_code + "'";
            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            DataRow dr = dt.Rows[0];
            if (int.Parse(dr["COUNT"].ToString()) > 0)
            {
                return true;
            }
            else return false;
        }

        /// <summary>
        /// 得到指定档号、指定标本编号所有图片名称
        /// </summary>
        /// <returns>返回dataset</returns>
        public DataSet GetPicNameWithSet(string zl_file_id, string bb_code)
        {
            string strSql = "SELECT * FROM dbo.BB_IMAGE_TBL WHERE ZL_FILE_ID = '" + zl_file_id + "' AND bb_code = '" + bb_code + "' ";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 得到指定档号、指定标本编号的记录信息
        /// </summary>
        /// <param name="zl_file_id"></param>
        /// <param name="bb_code"></param>
        /// <param name="hc"></param>
        /// <returns></returns>
        public DataSet GetPicAllInfoWithSet(string zl_file_id, string bb_code)
        {
            string strSql = "SELECT * FROM dbo.BB_IMAGE_TBL WHERE ZL_FILE_ID = '" + zl_file_id + "' AND BB_CODE = '" + bb_code + "' ";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        //根据主键ID取回对应的图片
        public DataSet GetInfoByID(int bbid)
        {
            string strSql = "SELECT * FROM dbo.BB_IMAGE_TBL WHERE ID =" + bbid ;
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }


       #region 将excel中的数据存储至sql

        /// <summary>
        /// 添加TBL数据至SQL
        /// </summary>
        /// <param name="zl_info"></param>
        /// <returns></returns>
        public int AddBBImageInfo(BB_IMAGE_TBL bbImg_info)
        {
            // 先处理普通数据
            string strSql = "INSERT INTO dbo.BB_IMAGE_TBL(ZL_FILE_ID,KS_NAME,BB_CODE,YS_NAME,YS_TYPE,";
            strSql += "CY_POS,REMARK,CJ_PEOPLE,PIC_NAME) VALUES(";

            StringBuilder sbSql = new StringBuilder(strSql);

            strSql += "'" + bbImg_info.O_ZL_FILE_ID + "',";
            strSql += "'" + bbImg_info.O_KS_NAME + "',";
            strSql += "'" + bbImg_info.O_BB_CODE + "',";
            strSql += "'" + bbImg_info.O_YS_NAME + "',";
            strSql += "'" + bbImg_info.O_YS_TYPE + "',";
            strSql += "'" + bbImg_info.O_CY_POS + "',";
            strSql += "'" + bbImg_info.O_REMARK + "',";
            strSql += "'" + bbImg_info.O_CJ_PEOPLE + "',";
            strSql += "'" + bbImg_info.O_PIC_NAME + "')";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 将DataSet存至SQL
        /// </summary>
        /// <param name="excelDS"></param>
        /// <returns></returns>
        public int AddDataSet2SQL(DataSet excelDS, string ZL_FILE_ID)
        {
            int nRow = excelDS.Tables[0].Rows.Count;
            for (int i = 0; i < nRow; i++)
            {
                
                BB_IMAGE_TBL bbImgTbl = new BB_IMAGE_TBL();
                bbImgTbl.O_ZL_FILE_ID = ZL_FILE_ID;//excelDS.Tables[0].Rows[2][0].ToString();
                bbImgTbl.O_KS_NAME = excelDS.Tables[0].Rows[i+5][1].ToString();
                bbImgTbl.O_BB_CODE = excelDS.Tables[0].Rows[i+5][2].ToString();
                bbImgTbl.O_YS_NAME = excelDS.Tables[0].Rows[i+5][3].ToString();
                bbImgTbl.O_YS_TYPE = excelDS.Tables[0].Rows[i+5][4].ToString();
                bbImgTbl.O_CY_POS = excelDS.Tables[0].Rows[i+5][5].ToString();
                bbImgTbl.O_REMARK = excelDS.Tables[0].Rows[i+5][6].ToString();
                bbImgTbl.O_CJ_PEOPLE =  excelDS.Tables[0].Rows[2][0].ToString();
                bbImgTbl.O_PIC_NAME = excelDS.Tables[0].Rows[i+5][2].ToString() + excelDS.Tables[0].Rows[i+5][3].ToString();

                if (AddBBImageInfo(bbImgTbl) != 1)
                {
                    return 0;
                }
            }

            return 1;
        }

        /// <summary>
        /// 通过excel路径名，将excel信息存储至SQL数据库
        /// </summary>
        /// <param name="excelPath"></param>
        /// <returns></returns>
        public int AddExcel2SQL(string excelPath, string ZL_FILE_ID)
        {
            ExcelReader er = new ExcelReader();

            DataSet ds = new DataSet();

            ds = er.ConvertBBExcelToDataTable(excelPath);

            if (AddDataSet2SQL(ds, ZL_FILE_ID) != 1)
            {
                return 0;
            }

            return 1;
        }

        #endregion

        /// <summary>
        /// 通过ID删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteBBImgInfoByID(int id)
        {
            string strSql = "DELETE FROM dbo.BB_IMAGE_TBL WHERE ID ='";
            strSql += id + "'";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 根据档号和钻孔号删除著录表数据
        /// </summary>
        /// <param name="file_id">著录表档号</param>
        /// <returns></returns>
         public int DeleteZLInfo(string file_id, string bb_code)
         {
             string strSql = "DELETE FROM dbo.BB_IMAGE_TBL WHERE FILE_ID ='" + file_id + "' AND BB_CODE = '" + bb_code + "' ";
 
             return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
         }

        /// <summary>
        /// 更新，需要知道记录的ID
        /// </summary>
        /// <param name="imgInfo"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateBBImgInfo(BB_IMAGE_TBL imgInfo, int id)
        {
            string strSql = "UPDATE dbo.BB_IMAGE_TBL ";

            strSql += "SET ZL_FILE_ID='" + imgInfo.O_ZL_FILE_ID + "',";
            strSql += "KS_NAME='" + imgInfo.O_KS_NAME + "',";
            strSql += "BB_CODE='" + imgInfo.O_BB_CODE + "',";
            strSql += "YS_NAME='" + imgInfo.O_YS_NAME + "',";
            strSql += "YS_TYPE='" + imgInfo.O_YS_TYPE + "',";
            strSql += " CY_POS='" + imgInfo.O_CY_POS + "',";
            strSql += " REMARK='" + imgInfo.O_REMARK + "',";
            strSql += " CJ_PEOPLE='" + imgInfo.O_CJ_PEOPLE + "',";
            strSql += " PIC_NAME='" + imgInfo.O_PIC_NAME + "'";

            strSql += "WHERE ID='" + id + "'";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }
    }
}

